﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using OgilvyOne.IDAL;

namespace OgilvyOne.SqlServerDAL
{
    public class ContactUs:IContactUs
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        /// <summary>
        /// 获取留言信息
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public IList getContactus(ContactUsModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CONTACT_ID,CONTACT_NAME,CONTACT_TEL,MOBILE,CONTACT_EMAIL,ADD_TIME,STATE from COFCO_CONTACT_US where 1=1");
            if (cum.Contactname != null && cum.Contactname!="")
            {
                strSql.Append(" and (CONTACT_NAME like '%" + cum.Contactname.ToString().Trim() + "%' ");
                strSql.Append(" or MESSAGE like '%" + cum.Contactname.ToString().Trim() + "%' ");
                strSql.Append(" or CONTACT_REPLY like '%" + cum.Contactname.ToString().Trim() + "%') ");
            }
            if (cum.Addtime.Year>1900)
            {
                
                strSql.Append(" and ADD_TIME >= '" + cum.Addtime.ToString().Trim() + "'");
                
            }
            if (cum.Endtime.Year>1900)
            {
                
                strSql.Append("  and ADD_TIME <= ' " + cum.Endtime.ToString().Trim() + "'");
            }
            if (cum.Contacttype > 0)
            {
                strSql.Append("  and CONTACT_TYPE =  " + (cum.Contacttype == 1 ? 0 : 1) + "");
            }
            strSql.Append(" order by ADD_TIME desc ");

            SqlParameter[] parms = new SqlParameter[] {
            };

            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql.ToString(), parms))
            {
                try
                {
                    while (rdr.Read())
                    {
                        ContactUsModel cumodel = new ContactUsModel();
                        cumodel.Contactid = rdr.GetInt32(0);
                        if (!rdr.IsDBNull(1)) cumodel.Contactname = rdr.GetString(1);
                        if (!rdr.IsDBNull(2)) cumodel.Contacttel = rdr.GetString(2);
                        if (!rdr.IsDBNull(3)) cumodel.Mobile = rdr.GetString(3);
                        if (!rdr.IsDBNull(4)) cumodel.Contactemail = rdr.GetString(4);
                        if (!rdr.IsDBNull(5)) cumodel.Addtime = rdr.GetDateTime(5);
                        if (!rdr.IsDBNull(6)) cumodel.State = rdr.GetInt32(6);

                        datalist.Add(cumodel);
                    }
                }
                catch(Exception ex)
                { }
            }
            return datalist;
        }

        public DataSet getContactusDs(ContactUsModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CONTACT_ID,CONTACT_NAME,CONTACT_SEX,CONTACT_TEL,MOBILE,CONTACT_EMAIL,CONTACT_BUSINESS,CONTACT_PRODUCT_TYPE,MESSAGE,ADD_TIME,CONTACT_BRAND,CONTACT_REPLY,STATE from COFCO_CONTACT_US where 1=1");
            if (cum.Contactname != null && cum.Contactname != "")
            {
                strSql.Append(" and (CONTACT_NAME like '%" + cum.Contactname.ToString().Trim() + "%' ");
                strSql.Append(" or MESSAGE like '%" + cum.Contactname.ToString().Trim() + "%' ");
                strSql.Append(" or CONTACT_REPLY like '%" + cum.Contactname.ToString().Trim() + "%') ");
            }
            if (cum.Addtime.Year > 1900)
            {

                strSql.Append(" and ADD_TIME >= '" + cum.Addtime.ToString().Trim() + "'");

            }
            if (cum.Endtime.Year > 1900)
            {

                strSql.Append("  and ADD_TIME <= ' " + cum.Endtime.ToString().Trim() + "'");
            }
            if (cum.Contacttype > 0)
            {
                strSql.Append("  and CONTACT_TYPE =  " + (cum.Contacttype == 1 ? 0 : 1) + "");
            }
            strSql.Append(" order by ADD_TIME desc ");

            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 查询一条留言
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        private const string COLUMN_SQL = "select CONTACT_ID,CONTACT_NAME,CONTACT_TEL,MOBILE,CONTACT_EMAIL,CONTACT_TYPE,CONTACT_BUSINESS,CONTACT_PRODUCT_TYPE,CONTACT_THEME,MESSAGE,ADD_TIME,CONTACT_BRAND,STATE,CONTACT_SEX,CONTACT_REPLY from COFCO_CONTACT_US where CONTACT_ID=@CONTACT_ID";
        public ContactUsModel selectContactus(int CONTACT_ID)
        {
            ContactUsModel cum = new ContactUsModel();
            SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@CONTACT_ID", SqlDbType.Int)};
                parms[0].Value = CONTACT_ID;


                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, COLUMN_SQL, parms))
                {
                    if (rdr.Read())
                    {
                        cum.Contactid = rdr.GetInt32(0);
                        if (!rdr.IsDBNull(1)) cum.Contactname = rdr.GetString(1);
                        if (!rdr.IsDBNull(2)) cum.Contacttel = rdr.GetString(2);
                        if (!rdr.IsDBNull(3)) cum.Mobile = rdr.GetString(3);
                        if (!rdr.IsDBNull(4)) cum.Contactemail = rdr.GetString(4);
                        if (!rdr.IsDBNull(5)) cum.Contacttype = rdr.GetInt32(5);
                        if (!rdr.IsDBNull(6)) cum.Contactbusiness = rdr.GetString(6);
                        if (!rdr.IsDBNull(7)) cum.Contactproducttype = rdr.GetString(7);
                        if (!rdr.IsDBNull(8)) cum.Contacttheme = rdr.GetString(8);
                        if (!rdr.IsDBNull(9)) cum.Message = rdr.GetString(9);
                        if (!rdr.IsDBNull(10)) cum.Addtime = rdr.GetDateTime(10);
                        if (!rdr.IsDBNull(11)) cum.Contactbrand = rdr.GetString(11);
                        if (!rdr.IsDBNull(12)) cum.State = rdr.GetInt32(12);
                        if (!rdr.IsDBNull(13)) cum.Contactsex = rdr.GetString(13);
                        if (!rdr.IsDBNull(14)) cum.Contactreply = rdr.GetString(14);
                    }
                }
                return cum;
        }
        /// <summary>
        /// 删除单行留言
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public bool deleteContactu(int CONTACT_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_CONTACT_US where CONTACT_ID = @CONTACT_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@CONTACT_ID", SqlDbType.Int,4)
             };
            parameters[0].Value = CONTACT_ID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 删除多行留言
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public bool DeleteContactus(string CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from COFCO_CONTACT_US where CONTACT_ID in (" + CID + ") ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }
        /// <summary>
        /// 新增留言
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public int Add(ContactUsModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into COFCO_CONTACT_US (CONTACT_NAME,CONTACT_TEL,MOBILE,CONTACT_EMAIL,CONTACT_TYPE,CONTACT_BUSINESS,CONTACT_PRODUCT_TYPE,CONTACT_THEME,MESSAGE,ADD_TIME,CONTACT_SEX,STATE,CONTACT_BRAND)");
            strSql.Append(" VALUES (@CONTACT_NAME,@CONTACT_TEL,@MOBILE,@CONTACT_EMAIL,@CONTACT_TYPE,@CONTACT_BUSINESS,@CONTACT_PRODUCT_TYPE,@CONTACT_THEME,@MESSAGE,getdate(),@CONTACT_SEX,0,@CONTACT_BRAND)");
            SqlParameter[] parameters = {
                    new SqlParameter("@CONTACT_NAME",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_TEL",SqlDbType.VarChar,20),
                    new SqlParameter("@MOBILE",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_EMAIL",SqlDbType.VarChar,100),
                    new SqlParameter("@CONTACT_TYPE",SqlDbType.Int),
                    new SqlParameter("@CONTACT_BUSINESS",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_PRODUCT_TYPE",SqlDbType.VarChar,50),
                    new SqlParameter("@CONTACT_THEME",SqlDbType.VarChar,50),
                    new SqlParameter("@MESSAGE",SqlDbType.VarChar,800),
                    new SqlParameter("@CONTACT_SEX",SqlDbType.VarChar,20),
                    new SqlParameter("@CONTACT_BRAND",SqlDbType.VarChar,50)
                                        };
            parameters[0].Value = cum.Contactname;
            parameters[1].Value = cum.Contacttel;
            parameters[2].Value = cum.Mobile;
            parameters[3].Value = cum.Contactemail;
            parameters[4].Value = cum.Contacttype;
            parameters[5].Value = cum.Contactbusiness;
            parameters[6].Value = cum.Contactproducttype;
            parameters[7].Value = cum.Contacttheme;
            parameters[8].Value = cum.Message;
            parameters[9].Value = cum.Contactsex;
            parameters[10].Value = cum.Contactbrand;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 回复留言（更新留言）
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public int Update(ContactUsModel cum)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE COFCO_CONTACT_US SET CONTACT_REPLY=@CONTACT_REPLY,STATE=@STATE WHERE CONTACT_ID = @CONTACT_ID");

            SqlParameter[] parameters = {
                    new SqlParameter("@CONTACT_REPLY",SqlDbType.VarChar,800),
                    new SqlParameter("@STATE",SqlDbType.Int),
                    new SqlParameter("@CONTACT_ID",SqlDbType.Int)
                        };
            parameters[0].Value = cum.Contactreply;
            parameters[1].Value = cum.State;
            parameters[2].Value = cum.Contactid;

            int ts = 0;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获取留言，回复信息
        /// </summary>
        /// <param> </param>
        /// <returns></returns>
        public IList getContactus(int contacttype)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MESSAGE,CONTACT_REPLY from COFCO_CONTACT_US where state=1 and CONTACT_TYPE="+contacttype);
            SqlParameter[] parms = new SqlParameter[] {
            };
            IList datalist = new ArrayList();
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql.ToString(), parms))
            {
                try
                {
                    while (rdr.Read())
                    {
                        ContactUsModel cumodel = new ContactUsModel();
                        if (!rdr.IsDBNull(0)) cumodel.Message = rdr.GetString(0);
                        if (!rdr.IsDBNull(1)) cumodel.Contactreply = rdr.GetString(1);
                        datalist.Add(cumodel);
                    }
                }
                catch (Exception ex)
                { }
            }
            return datalist;
        }
    }
}
